package service.impl;

import entity.StudentDO;
import req.StudentRequest;
import res.TableDTO;
import service.StudentService;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class StudentServiceImpI implements StudentService {
    // String user;

    @Override
    public TableDTO retrieveStudent(StudentRequest request) {
        StringBuilder sql = new StringBuilder();

        sql.append("select * from stu ");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where sname like '%" + request.getSearchKey().trim() + "%'");
        }
        sql.append("order by sno desc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查寻记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from stu ");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where sname like '%" + request.getSearchKey().trim() + "%'");
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean add(StudentDO studentDO) {
        StringBuilder sql = new StringBuilder();
        //修改版
        //sql.append(" insert into stu(sno,sname,ssex,sage,sdept,sspe,sclass) ");
        sql.append(" insert into stu ");
        sql.append(" values(?,?,?,?,?,?,?)");

        PreparedStatement ps = null;
        Connection conn = null;
        //ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, studentDO.getSno());
            ps.setString(2, studentDO.getSname());
            ps.setString(3, studentDO.getSex());
            ps.setString(4, studentDO.getAge());
            ps.setString(5, studentDO.getDept());
            ps.setString(6, studentDO.getSpe());
            ps.setString(7, studentDO.getSclass());

            return ps.executeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    @Override
    public StudentDO getById(int selectStudentId) {
        StringBuilder sql = new StringBuilder("select * from stu where sno = ? ");
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        StudentDO studentDO = new StudentDO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, selectStudentId);
            rs = ps.executeQuery();
            while (rs.next()) {

                int sno = rs.getInt("sno");
                String sname = rs.getString("sname");
                String ssex = rs.getString("ssex");
                String sage = rs.getString("sage");
                String sdept = rs.getString("sdept");
                String sspe = rs.getString("sspe");
                String sclass = rs.getString("sclass");
//                int cno = rs.getInt("cno");
                //String cname = rs.getString("cname");
                //float grade = rs.getFloat("grades");
                //studentDO.setNo(String.valueOf(sno));
                studentDO.setSno(sno);
                studentDO.setSname(sname);
                studentDO.setSex(ssex);
                studentDO.setAge(sage);
                studentDO.setDept(sdept);
                studentDO.setSpe(sspe);
                studentDO.setSclass(sclass);
            }
            return studentDO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean update(StudentDO studentDO) {
        StringBuilder sql = new StringBuilder();
        //修改版
        sql.append(" update stu set sname = ?,ssex = ?,sage = ?, sdept = ?, sspe = ? ,sclass = ? ");
        sql.append(" where sno = ? ");

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            //ps.setString(1,studentDO.getNo());
            ps.setString(1, studentDO.getSname());
            ps.setString(2, studentDO.getSex());
            ps.setString(3, studentDO.getAge());
            ps.setString(4, studentDO.getDept());
            ps.setString(5, studentDO.getSpe());
            ps.setString(6, studentDO.getSclass());
            ps.setInt(7, studentDO.getSno());

            return ps.executeUpdate() == 1;
            //查寻记录
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    @Override
    public boolean delete(int[] selectStudentIds) {
        StringBuilder sql = new StringBuilder();
        //修改版
        sql.append(" delete from stu where sno in ( ");
        int length = selectStudentIds.length;
        for (int i = 0; i < selectStudentIds.length; i++) {
            if (i == length - 1) {
                sql.append(" ? ");
            } else {
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {
                //设置参数
                ps.setInt(i + 1, selectStudentIds[i]);
            }
            return ps.executeUpdate() == length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //查寻到的记录
            Vector<Object> oneRecord = new Vector<>();
            int sno = rs.getInt("sno");
            String sname = rs.getString("sname");
            String ssex = rs.getString("ssex");
            String sage = rs.getString("sage");
            String sdept = rs.getString("sdept");
            String sspe = rs.getString("sspe");
            String sclass = rs.getString("sclass");
            oneRecord.addElement(sno);
            oneRecord.addElement(sname);
            oneRecord.addElement(ssex);
            oneRecord.addElement(sage);
            oneRecord.addElement(sdept);
            oneRecord.addElement(sspe);
            oneRecord.addElement(sclass);
            data.addElement(oneRecord);
        }
        return data;
    }

    private Vector<Vector<Object>> fillData2(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //查寻到的记录
            Vector<Object> oneRecord = new Vector<>();
            int sno = rs.getInt("sno");
            String sname = rs.getString("sname");
            String ssex = rs.getString("ssex");
            String sdept = rs.getString("sdept");
            String sspe = rs.getString("sspe");
            String sclass = rs.getString("sclass");
            int cno = rs.getInt("cno");
            String cname = rs.getString("cname");
            float grade = rs.getFloat("grades");
            oneRecord.addElement(sno);
            oneRecord.addElement(sname);
            oneRecord.addElement(ssex);
            oneRecord.addElement(sdept);
            oneRecord.addElement(sspe);
            oneRecord.addElement(sclass);
            oneRecord.addElement(cno);
            oneRecord.addElement(cname);
            oneRecord.addElement(grade);
            data.addElement(oneRecord);
        }
        return data;
    }
}
